﻿<###################################################################################################################################
 # Name:	WaitingTasks.ps1
 # Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 # Summary:	Collect details on waiting tasks from a list of servers.
 #
 # PowerShell.exe -NoProfile -NonInteractive -Command "& 'C:\DBAdmin\SQL Waits\WaitingTasks.ps1'"
 #
 # $ErrorActionPreference= "SilentlyContinue";
 # $VerbosePreference= "Continue";
 # $ServerListFile= "C:\Users\EZVHNL\Documents\T-SQL Scripts\DBAdmin ToolKit\PoSh\Wait Statistics\Servers.txt";
 #>

    $cn= New-Object System.Data.SqlClient.SQLConnection("Data Source=BBA-Phl-vDBAdmin.BBAAviation.net;Initial Catalog=DBAdmin;Integrated Security=SSPI;");
    $cn.Open();

	$cmd_InsertTasks= New-Object System.Data.SqlClient.SqlCommand;
	$cmd_InsertTasks.CommandText= "EXECUTE dbo.Log_SQLWaitingTasks @server_name, @server_time, @session_id, @exec_context_id, @blocking_session_id, @blocking_exec_context_id, @wait_type, @wait_duration_ms, @dbname, @host_name, @program_name, @login_name, @open_transaction_count, @command, @text";
	[void]$cmd_InsertTasks.Parameters.Add("@server_name"				, "NVarChar"		,   128, "server_name");
	[void]$cmd_InsertTasks.Parameters.Add("@server_time"				, "DateTimeOffset"	, $null, "server_time");
	[void]$cmd_InsertTasks.Parameters.Add("@session_id"					, "SmallInt"		, $null, "session_id");
	[void]$cmd_InsertTasks.Parameters.Add("@exec_context_id"			, "SmallInt"		, $null, "exec_context_id");
	[void]$cmd_InsertTasks.Parameters.Add("@blocking_session_id"		, "SmallInt"		, $null, "blocking_session_id");
	[void]$cmd_InsertTasks.Parameters.Add("@blocking_exec_context_id"	, "SmallInt"		, $null, "blocking_exec_context_id");
	[void]$cmd_InsertTasks.Parameters.Add("@wait_type"					, "NVarChar"		,    60, "wait_type");
	[void]$cmd_InsertTasks.Parameters.Add("@wait_duration_ms"			, "Int"				, $null, "wait_duration_ms");
	[void]$cmd_InsertTasks.Parameters.Add("@dbname"						, "NVarChar"		,   128, "dbname");
	[void]$cmd_InsertTasks.Parameters.Add("@host_name"					, "NVarChar"		,   128, "host_name");
	[void]$cmd_InsertTasks.Parameters.Add("@program_name"				, "NVarChar"		,   128, "program_name");
	[void]$cmd_InsertTasks.Parameters.Add("@login_name"					, "NVarChar"		,   128, "login_name");
	[void]$cmd_InsertTasks.Parameters.Add("@open_transaction_count"		, "Int"				, $null, "open_transaction_count");
	[void]$cmd_InsertTasks.Parameters.Add("@command"					, "NVarChar"		,    32, "command");
	[void]$cmd_InsertTasks.Parameters.Add("@text"						, "NVarChar"		, $null, "text");
	$cmd_InsertTasks.Connection= $cn;

    $ServerListFile= "$PSScriptRoot\Servers.txt";
    foreach ($ServerName in (Get-Content $ServerListFile)) {
        Write-Verbose $ServerName;
        $da_Get= New-Object System.Data.SqlClient.SqlDataAdapter("SELECT * FROM DBAdmin.ShowWaitingTasks;", "Data Source=$ServerName;Initial Catalog=master;Integrated Security=SSPI;")
        $ds_Get= New-Object System.Data.DataSet;
        [void]$da_Get.Fill($ds_Get);
        Write-Verbose $ds_Get.Tables[0].Rows.Count;

        # sys.dm_os_waiting_tasks
        foreach ($r in $ds_Get.Tables[0]) {
			$cmd_InsertTasks.Parameters["@server_name"				].Value= $r.server_name;
			$cmd_InsertTasks.Parameters["@server_time"				].Value= $r.server_time;
			$cmd_InsertTasks.Parameters["@session_id"				].Value= $r.session_id;
			$cmd_InsertTasks.Parameters["@exec_context_id"			].Value= $r.exec_context_id;
			$cmd_InsertTasks.Parameters["@blocking_session_id"		].Value= $r.blocking_session_id;
			$cmd_InsertTasks.Parameters["@blocking_exec_context_id"	].Value= $r.blocking_exec_context_id;
			$cmd_InsertTasks.Parameters["@wait_type"				].Value= $r.wait_type;
			$cmd_InsertTasks.Parameters["@wait_duration_ms"			].Value= $r.wait_duration_ms;
			$cmd_InsertTasks.Parameters["@dbname"					].Value= $r.dbname;
			$cmd_InsertTasks.Parameters["@host_name"				].Value= $r.host_name;
			$cmd_InsertTasks.Parameters["@program_name"				].Value= $r.program_name;
			$cmd_InsertTasks.Parameters["@login_name"				].Value= $r.login_name;
			$cmd_InsertTasks.Parameters["@open_transaction_count"	].Value= $r.open_transaction_count;
			$cmd_InsertTasks.Parameters["@command"					].Value= $r.command;
			$cmd_InsertTasks.Parameters["@text"						].Value= $r.text;
			[void]$cmd_InsertTasks.ExecuteNonQuery();
        }

        $ds_Get.Dispose();
        $da_Get.Dispose();
    };
    $cn.Close();